package com.neutech.dao.impl;

import com.neutech.dao.UserDAO;
import com.neutech.entity.User;
import com.neutech.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description: TODO(这里用一句话描述这个类的作用)
 * @Author ${秦}
 * @Date 2021/3/12 9:21
 */
public class UserDAOImpl implements UserDAO {

    @Override
    public List<User> selectAll() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<User> users = new ArrayList<>();
        String sql = "select " +
                "id,user_name,user_password,user_phone,user_addr," +
                "user_createTime,user_updateTime,user_status from t_user";
        try{
            conn = DBUtil.getConnection();
            stmt = DBUtil.getStatement(conn);
            rs = DBUtil.getResultSet(stmt,sql);
            while (rs.next()){
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("user_name"));
                user.setPassword(rs.getString("user_password"));
                user.setPhone(rs.getString("user_phone"));
                user.setAddress(rs.getString("user_addr"));
                user.setCreateTime(rs.getTimestamp("user_createTime"));
                user.setUpdateTime(rs.getTimestamp("user_updateTime"));
                user.setStatus(rs.getInt("user_status"));

                users.add(user);
            }
        }catch (Exception e){
            e.printStackTrace();
            //当出现任何异常时，事务不提交，回滚到提交之前的状态。
            DBUtil.rollback(conn);
        }finally {
             DBUtil.close(rs,stmt,conn);
        }
        return users;
    }

    @Override
    public User selectById(Integer id) {
        Connection conn = null;
        PreparedStatement prep = null;
        ResultSet rs = null;
        User user = null;
        String sql = "select " +
                "id,user_name,user_password,user_phone,user_addr," +
                "user_createTime,user_updateTime,user_status from t_user " +
                "where id = ?";
        try{
            conn = DBUtil.getConnection();
            prep = DBUtil.getPreparedStatement(conn,sql);
            prep.setInt(1,id);
            rs = DBUtil.getResultSet(prep);
            if (rs.next()){
                user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("user_name"));
                user.setPassword(rs.getString("user_password"));
                user.setPhone(rs.getString("user_phone"));
                user.setAddress(rs.getString("user_addr"));
                user.setCreateTime(rs.getTimestamp("user_createTime"));
                user.setUpdateTime(rs.getTimestamp("user_updateTime"));
                user.setStatus(rs.getInt("user_status"));

            }
        }catch (Exception e){
            e.printStackTrace();
            //当出现任何异常时，事务不提交，回滚到提交之前的状态。
            DBUtil.rollback(conn);
        }finally {
            DBUtil.close(rs,prep,conn);
        }
        return user;
    }

    @Override
    public void insert(User user) {
        Connection conn = null;
        PreparedStatement prep = null;
        String sql = "insert into t_user values(null,?,?,?,?,now(),now(),1) ";
        try{
            conn = DBUtil.getConnection();
            prep = DBUtil.getPreparedStatement(conn,sql);

                prep.setString(1, user.getName());
                prep.setString(2, user.getPassword());
                prep.setString(3, user.getPhone());
                prep.setString(4, user.getAddress());

                prep.executeUpdate();
            } catch (Exception e) {
                e.printStackTrace();
                DBUtil.rollback(conn);
            }finally {
                DBUtil.close(prep,conn);
            }
        }


    @Override
    public void delete(Integer id) {
        Connection conn = null;
        PreparedStatement prep = null;
        String sql = "delete from t_user where id = ? ";
        try{
            conn = DBUtil.getConnection();
            prep = DBUtil.getPreparedStatement(conn,sql);

            prep.setInt(1, id);


            prep.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            DBUtil.rollback(conn);
        }finally {
            DBUtil.close(prep,conn);
        }
    }

    @Override
    public void update(User user) {
        Connection conn = null;
        PreparedStatement prep = null;
        String sql = "update t_user set user_name = ?,user_password = md5(?)," +
                "user_phone = ?, user_addr = ?,user_updateTime = now() where id=?";
        try{
            conn = DBUtil.getConnection();
            prep = DBUtil.getPreparedStatement(conn,sql);

            prep.setString(1, user.getName());
            prep.setString(2, user.getPassword());
            prep.setString(3, user.getPhone());
            prep.setString(4, user.getAddress());
            prep.setInt(5,user.getId());

            prep.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            DBUtil.rollback(conn);
        }finally {
            DBUtil.close(prep,conn);
        }
    }
}
